Re: OS X driver - Mailing list pgsql-odbc

Actually, I replied to Avi a while back but now I see I didn't CC my
message to this list (Sorry!).  Avi said he was able to fix his
problem with my information, which I've included below.

- Jeff

>---- BEGIN OLD MESSAGE -----------------------------------------<

I've heard Apple's GUI ODBC tools just muck things up.  There are a
few other gotchas:

1) Data source names can only contain 0-9, A-Z, and a-z.  No
underscores or other characters.

2) The driver entry in odbc.ini must be a fully qualified path to the
driver file, not an alias or link.

3) Sometimes iODBC will create empty ".odbc.ini" files in user's home
directories.  Because the iODBC driver manager will load this empty
file first, subsequent attempts to connect to data sources fail.  If
you're not using per-user odbc.ini files, check each home directory
(including /var/root) for these files and remove them.


The following worked for me ...

First, add a "host" access line in pg_hba.conf exists for ODBC access
if you don't already have one.

Second, run these commands in the terminal and see what they output.
They will show you where the driver manager expects to find it's
configuration files.

    iodbc-config --odbcini
    iodbc-config --odbcinstini

They probably say (respectively):

    /Library/ODBC/odbc.ini
    /Library/ODBC/odbcinst.ini

Now, make odbc.ini and odbcinst.ini at the specified location.
Here's my odbcinst.ini (your driver might be in a different location):

    [ODBC Drivers]
    PostgreSQL = Installed

    [PostgreSQL]
    Description = PostgreSQL ODBC driver
    Driver = /usr/local/lib/psqlodbc.so
    Setup  = /usr/local/lib/psqlodbc.so
    FileUsage = 1


Here's an example odbc.ini:

    [ODBC Data Sources]
    test = PostgreSQL

    [test]
    Driver           = /usr/local/lib/psqlodbc.so
    Description      = test database
    Trace            = No
    TraceFile        = /tmp/sql.log
    Database         = test
    ServerName       = localhost
    Port             = 5432
    UserName         = username
    Password         = password
    Protocol         = 6.4
    ReadOnly         = No
    RowVersioning    = No
    ShowSystemTables = Yes
    ShowOidColumn    = No
    FakeOidIndex     = No
    ConnSettings     =


Finally, try connecting to your database using odbctest.  For the
example entries in odbc.ini above, you would do:

    odbctest "DSN=test;UID=username;PWD=password;"

You should get a "SQL>" prompt and be able to execute SQL commands
against your database.  Type "quit" to exit.

Your ODBC application may use the ODBCINI and ODBCINSTINI environment
variables.  These should be set to the correct locations of odbc.ini
and odbcinst.ini.  To set them, use these commands:

    ODBCINI=/Library/ODBC/odbc.ini
    export ODBCINI
    ODBCINSTINI=/Library/ODBC/odbcinst.ini
    export ODBCINSTINI


>---- END OLD MESSAGE -------------------------------------------<


>I think this is a question of driver manager, I investigated this
>sometime ago and I even grepped the sources to find all the keywords
>but I did not keep notes...
>
>Anyway, I have these keywords and they work. I do not know if there
>are any aliases, but I did use the keywords you gave when I was
>working in MacOS9 (I did most of my ODBC on MacOS9, from about 4
>years ago).
>
>Giuliano
>
>At 17:23 -0400 2003/06/20, Jo Dillon wrote:
>>On Fri, Jun 20, 2003 at 10:08:34PM +0100, Giuliano Gavazzi wrote:
>>>  THis should be enough, for example:
>>>
>>>  Servername    127.0.0.1
>>>  Database    testdb
>>>  Username    bop
>>>  ReadOnly    NO
>>>  Password    IamNotSureThisIsUsedAnyway
>>
>>   Hmm, I'd use:
>>
>>HOSTNAME 127.0.0.1
>>DATABASE testdb
>>UID foo
>>PWD <blank>
>>
>>   Or is that hopelessly Microsoftish of me?
>>
>>--
>>
>>    Jo
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>       subscribe-nomail command to majordomo@postgresql.org so that your
>>       message can get through to the mailing list cleanly
>
>
>--
>H U M P H
>    || |||
>  software
>
>Java & C++ Server/Client/Human Interface applications on MacOS - MacOS X
>http://www.humph.com/
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly

--

Jeffrey Bohmer
VisionLink, Inc.
_________________________________
303.402.0170
www.visionlink.org
_________________________________
People. Tools. Change. Community.